Checking env variables:
CMSC408_HW6_USER = sp25_torresc6
CMSC408_HW6_PASSWORD = Shout4_torresc6_joY
CMSC408_HW6_HOST = cmsc-vcu.com
CMSC408_HW6_DB_NAME = sp25_torresc6_hrHomework 6 - Creating a resume database
This Homework, we will be walking through the steps it takes to connect to a database, and then produce equations on this database using SQL and SQL functions we developed in the My-DDL.SQL file. Both that file and this one will be helpful to understand what is taking place. We are going to be manipulating our HR database by editing the People, Skills, and Peopleskills tables! We will walk through each table, before performing filters and functions on them in the Queries section below! Welcome to Homework 4!
GITHUB URL: https://github.com/cmsc-vcu/cmsc408-sp2025-hw6-serrotrehpotsirhc.git
Overview and description
The database will represent a company-wide resume, capturing the collective skills and certifications of all the people in your company. The database will capture skills, employees and their roles within the company. The database will help our employer find employees with specific skills, list the skills of any employee and help find gaps in the skill sets of employees in specific roles.
Crows-foot diagram
This is the Crows Foot diagram for this database. People have skills. People can have many skills, and skills can each have many people. Both People and Skills are each One to Many toward each other, making both Many to Many.
erDiagram
people ||--o{ peopleskills : has
skills ||--o{ peopleskills : includes
people {
int id PK
string first_name
string last_name
string email
string linkedin_url
string headshot_url
string discord_handle
string brief_bio
date date_joined
}
skills {
int id PK
string name
string description
string tag
string url
string extra
}
peopleskills {
int id PK
int skills_id FK
int people_id FK
datetime date_acquired
}
Examples of data in the database
The following sections provide an overview of the schema including table names, and number of rows and columns in each table.
Each table is presented along with a description of it’s contents.
Tables and metrics in the database
| TableName | RecordCount | ColumnCount |
|---|---|---|
| countries | 25.0 | 3 |
| departments | 27.0 | 4 |
| emp_details_view | NULL | 16 |
| employees | 107.0 | 11 |
| job_grades | 6.0 | 3 |
| job_history | 10.0 | 5 |
| jobs | 19.0 | 4 |
| locations | 23.0 | 6 |
| people | 10.0 | 9 |
| peopleskills | 26.0 | 4 |
| regions | 4.0 | 2 |
| skills | 8.0 | 6 |
| Total Rows: 12, Total Columns: 3 | ||
People table
The people table contains a SQL Select call to print the entire people table, using the *.
Below is a list of data in the people table.
| id | first_name | last_name | linkedin_url | headshot_url | discord_handle | brief_bio | date_joined | |
|---|---|---|---|---|---|---|---|---|
| 1 | Avery | Person 1 | avery1@example.com | https://linkedin.com/in/avery1 | https://example.com/headshots/avery.jpg | @avery01 | Fitness enthusiast and tech blogger. | 2025-01-01 |
| 2 | Jordan | Person 2 | jordan2@example.com | https://linkedin.com/in/jordan2 | https://example.com/headshots/jordan.jpg | @jordan02 | Mechanical engineer who loves extreme sports. | 2025-01-01 |
| 3 | Taylor | Person 3 | taylor3@example.com | https://linkedin.com/in/taylor3 | https://example.com/headshots/taylor.jpg | @taylor03 | Writer and hobbyist swimmer. | 2025-01-01 |
| 4 | Morgan | Person 4 | morgan4@example.com | https://linkedin.com/in/morgan4 | https://example.com/headshots/morgan.jpg | @morgan04 | Artist focused on sustainable designs. | 2025-01-01 |
| 5 | Riley | Person 5 | riley5@example.com | https://linkedin.com/in/riley5 | https://example.com/headshots/riley.jpg | @riley05 | Racing enthusiast and cliff diver. | 2025-01-01 |
| 6 | Skylar | Person 6 | skylar6@example.com | https://linkedin.com/in/skylar6 | https://example.com/headshots/skylar.jpg | @skylar06 | Tech consultant with a passion for flying. | 2025-01-01 |
| 7 | Casey | Person 7 | casey7@example.com | https://linkedin.com/in/casey7 | https://example.com/headshots/casey.jpg | @casey07 | Athlete and mural enthusiast. | 2025-01-01 |
| 8 | Quinn | Person 8 | quinn8@example.com | https://linkedin.com/in/quinn8 | https://example.com/headshots/quinn.jpg | @quinn08 | Entrepreneur and adventure lover. | 2025-01-01 |
| 9 | Reese | Person 9 | reese9@example.com | https://linkedin.com/in/reese9 | https://example.com/headshots/reese.jpg | @reese09 | Freelancer and swimmer. | 2025-01-01 |
| 10 | Parker | Person 10 | parker10@example.com | https://linkedin.com/in/parker10 | https://example.com/headshots/parker.jpg | @parker10 | Strength coach and motivational speaker. | 2025-01-01 |
| Total Rows: 10, Total Columns: 9 | ||||||||
Skills table
The skills table contains a SQL Select call to print the entire skills table, using the *.
Below is a list of data in the skills table.
| id | name | description | tag | url | time_commitment |
|---|---|---|---|---|---|
| 1 | Rope Jumping | Jumping Rope | Skill 1 | www.google.com | NULL |
| 2 | Sky Diving | Jumping Rope | Skill 2 | www.google.com | NULL |
| 3 | Race Car Driving | Jumping Rope | Skill 3 | www.google.com | NULL |
| 4 | Heavy Lifting | Jumping Rope | Skill 4 | www.google.com | NULL |
| 5 | Swimmer | Jumping Rope | Skill 5 | www.google.com | NULL |
| 6 | Cliff Jumping | Jumping Rope | Skill 6 | www.google.com | NULL |
| 7 | Mural Artist | Jumping Rope | Skill 7 | www.google.com | NULL |
| 8 | Author | Jumping Rope | Skill 8 | www.google.com | NULL |
| Total Rows: 8, Total Columns: 6 | |||||
PeopleSkills table
The peopleskills table contains a SQL Select call to print the entire peopleskils table, using the *.
Below is a list of data in the peopleskills table.
| id | skills_id | people_id | date_acquired |
|---|---|---|---|
| 1 | 1 | 1 | 2024-01-01 |
| 2 | 3 | 1 | 2024-01-01 |
| 3 | 6 | 1 | 2024-01-01 |
| 4 | 3 | 2 | 2024-01-01 |
| 5 | 4 | 2 | 2024-01-01 |
| 6 | 5 | 2 | 2024-01-01 |
| 7 | 1 | 3 | 2024-01-01 |
| 8 | 5 | 3 | 2024-01-01 |
| 9 | 3 | 5 | 2024-01-01 |
| 10 | 6 | 5 | 2024-01-01 |
| 11 | 2 | 6 | 2024-01-01 |
| 12 | 3 | 6 | 2024-01-01 |
| 13 | 4 | 6 | 2024-01-01 |
| 14 | 3 | 7 | 2024-01-01 |
| 15 | 5 | 7 | 2024-01-01 |
| 16 | 6 | 7 | 2024-01-01 |
| 17 | 1 | 8 | 2024-01-01 |
| 18 | 3 | 8 | 2024-01-01 |
| 19 | 5 | 8 | 2024-01-01 |
| 20 | 6 | 8 | 2024-01-01 |
| 21 | 2 | 9 | 2024-01-01 |
| 22 | 5 | 9 | 2024-01-01 |
| 23 | 6 | 9 | 2024-01-01 |
| 24 | 1 | 10 | 2024-01-01 |
| 25 | 4 | 10 | 2024-01-01 |
| 26 | 5 | 10 | 2024-01-01 |
| Total Rows: 26, Total Columns: 4 | |||
Queries
List skill id, name and tag ordered by name
| id | name | tag |
|---|---|---|
| 8 | Author | Skill 8 |
| 6 | Cliff Jumping | Skill 6 |
| 4 | Heavy Lifting | Skill 4 |
| 7 | Mural Artist | Skill 7 |
| 3 | Race Car Driving | Skill 3 |
| 1 | Rope Jumping | Skill 1 |
| 2 | Sky Diving | Skill 2 |
| 5 | Swimmer | Skill 5 |
| Total Rows: 8, Total Columns: 3 | ||
List people names and email addresses ordered by last_name
| first_name | last_name | |
|---|---|---|
| Avery | Person 1 | avery1@example.com |
| Parker | Person 10 | parker10@example.com |
| Jordan | Person 2 | jordan2@example.com |
| Taylor | Person 3 | taylor3@example.com |
| Morgan | Person 4 | morgan4@example.com |
| Riley | Person 5 | riley5@example.com |
| Skylar | Person 6 | skylar6@example.com |
| Casey | Person 7 | casey7@example.com |
| Quinn | Person 8 | quinn8@example.com |
| Reese | Person 9 | reese9@example.com |
| Total Rows: 10, Total Columns: 3 | ||
List skill names of Person 1
| name |
|---|
| Rope Jumping |
| Race Car Driving |
| Cliff Jumping |
| Total Rows: 3, Total Columns: 1 |
List people names with Skill 6
| first_name | last_name |
|---|---|
| Avery | Person 1 |
| Riley | Person 5 |
| Casey | Person 7 |
| Quinn | Person 8 |
| Reese | Person 9 |
| Total Rows: 5, Total Columns: 2 | |
List names and email addresses of people without skills
| first_name | last_name | |
|---|---|---|
| Morgan | Person 4 | morgan4@example.com |
| Total Rows: 1, Total Columns: 3 | ||
Reflection
This assignment took forever! It took forever for me to get connected to the database. I had to run .env and make sure the script was picking up the credentials. Then I found out it was related to an encoding issue I ran into when I accidently produced a file from the SQLTools generated connection. I cleaned up my mistakes after a while, and finally got into the DDL and SQL coding! This took up so much time, and I watched both lectures!
The lectures jump straight to the assignment with no server connectivity issue. It was a whole thing for me! However, after getting it all worked out, it was a satisfying practice with SQL Tools, and I am looking forward to our final assignments. I am going to get into contact with my team soon to set up a way to start thinking of how we are going to implement our country club database. Thank you!
CMSC408 - Homework 6 - Spring 2025
Welcome to Homework 4! Here I am going to break down the summary, purpose of this assginment, and which files we will be editing! Thank you!
Summary
Homework 6 is all about working with a databse using SQL, and then creating an HTML rendered by Quarto, which performs functions on this database using SQL inside of python code.
Where are the important files
We store our QMD inside the reports folder. Report.QMD holds the python code which we generate into HTML. Inside of the Python, we hold snippets of SQL, using the functions we defined in helpers.py. These SQL pieces manipulate, filter, and extract our database.
Database Construction
We edited the database we have connected to through our remote poetry connection, through the MY-DDL File, which is where we populate and create each table of the database we are working with today!
Installations
We have installed poetry. Specifically for this homework, I didn’t need to install anything.
Running
We run this assignment through rendering the quarto, which interacts with the database through a remote connection. After, establishing connection, this Report.QMD file is converted to HTML, and it runs the Python code, which interacts with the database. We populated this database beforehand in My-DDL.SQL!
Thank you! That is all! Really frusturating assignment, but glad it worked out!